package cn.topevery.garden.dal.dao.impl;

import cn.topevery.garden.dal.dao.GreTreeFamousDao;
import cn.topevery.garden.entity.po.GreTreeFamous;
import cn.topevery.garden.entity.query.GreTreeFamousQuery;
import cn.topevery.garden.entity.query.GreTreeFamousRecordQuery;
import cn.topevery.garden.entity.vo.GreTreeFamousRecordVO;
import cn.topevery.garden.entity.vo.GreTreeFamousVO;
import cn.topevery.garden.framework.base.BaseDaoImpl;
import hw.topevery.framework.db.CommandType;
import hw.topevery.framework.db.entity.DataPager;
import hw.topevery.framework.db.entity.SqlQueryMate;
import hw.topevery.framework.db.enums.ScriptConditionEnum;
import hw.topevery.framework.dto.PointDto;
import hw.topevery.framework.entity.DbCsp;
import hw.topevery.framework.entity.KeyValue;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * 古树名木表
 *
 * @author code-generate
 * @date 2022-01-05
 */
@Repository
public class GreTreeFamousDaoImpl extends BaseDaoImpl<GreTreeFamous> implements GreTreeFamousDao {
    @Override
    public <P extends DbCsp> List<GreTreeFamous> getSearch(P para) {
        return null;
    }


    @Override
    public DataPager<GreTreeFamousVO> listPage(GreTreeFamousQuery query) {
        String table = "t_gre_tree_famous tgtf";

        String columns = "tgtf.*";

        int pageIndex = query.pageIndex - 1;
        int pageSize = query.pageSize;

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns).setOrderBy("tgtf.c_code asc");
        StringBuilder sqlBuilder = new StringBuilder();
        if(ObjectUtils.isNotEmpty(query.getMapRangesPoints())){
            sqlBuilder.append(" ST_Distance(ST_GeomFromText(CONCAT('POINT(',c_lng,' ',c_lat,')')),ST_GeomFromText('MULTIPOLYGON( ((");

            for(int j=0;j<query.getMapRangesPoints().size();j++){
                PointDto point = query.getMapRangesPoints().get(j);
                if(j>0){
                    sqlBuilder.append(",");
                }
                sqlBuilder.append(point.getLng()+ " "+ point.getLat());
            }
            sqlBuilder.append(")))') )");
        }

        sqlQueryMate
                .where(true, "tgtf.c_db_status", ScriptConditionEnum.Eq, 0)
                .where(StringUtils.isNoneBlank(query.beginDate) && StringUtils.isNoneBlank(query.endDate), "c_db_create_date", ScriptConditionEnum.GreaterThanEq, query.beginDate)
                .where(StringUtils.isNoneBlank(query.beginDate) && StringUtils.isNoneBlank(query.endDate), "c_db_create_date", ScriptConditionEnum.LessThanEq, query.endDate)
                .where(StringUtils.isNoneBlank(query.getCommunityCode()), "c_community_code", ScriptConditionEnum.Like, query.getCommunityCode())
                .where(StringUtils.isNoneBlank(query.getCodeTwo()), "c_code_two", ScriptConditionEnum.Like, query.getCodeTwo())
                .where(StringUtils.isNoneBlank(query.getTreeState()), "c_tree_state", ScriptConditionEnum.Like, query.getTreeState())
                .where(StringUtils.isNoneBlank(query.getTreeAttr()), "c_tree_attr", ScriptConditionEnum.Like, query.getTreeAttr())
                .where(StringUtils.isNoneBlank(query.getFamousTypeName()), "c_famous_type_name", ScriptConditionEnum.Like, query.getFamousTypeName())
                .where(ObjectUtils.isNotEmpty(query.getFamousTypeId()), "c_famous_type_id", ScriptConditionEnum.Eq, query.getFamousTypeId())
                .where(StringUtils.isNoneBlank(query.getKeyWord()), "concat(ifnull(c_code_two, ''), ifnull(c_name, ''), ifnull(c_distract_name, ''), ifnull(c_tree_state, ''), ifnull(c_tree_old, ''))",
                        ScriptConditionEnum.Like, query.getKeyWord())
                .where(StringUtils.isNotBlank(query.getCode()), "tgtf.c_code", ScriptConditionEnum.Like, query.getCode())
                .where(StringUtils.isNotBlank(query.getName()), "tgtf.c_name", ScriptConditionEnum.Like, query.getName())
                .where(StringUtils.isNotBlank(query.getTreeKind()), "tgtf.c_tree_kind", ScriptConditionEnum.Like, query.getTreeKind())
                .where(StringUtils.isNotBlank(query.getAloneLoad()), "tgtf.c_alone_load", ScriptConditionEnum.Like, query.getAloneLoad())
                .where(StringUtils.isNotBlank(query.getDistractId()), "tgtf.c_distract_id", ScriptConditionEnum.Eq, query.getDistractId())
                .where(StringUtils.isNotBlank(query.getStreetId()), "tgtf.c_street_id", ScriptConditionEnum.Eq, query.getStreetId())
                .where(StringUtils.isNotBlank(query.getCommunityId()), "tgtf.c_community_id", ScriptConditionEnum.Like, query.getCommunityId())
                .where(StringUtils.isNotBlank(query.beginDate), "tgtf.c_db_create_date", ScriptConditionEnum.GreaterThanEq, query.beginDate)
                .where(StringUtils.isNotBlank(query.endDate), "tgtf.c_db_create_date", ScriptConditionEnum.LessThanEq, query.endDate)
                .where(StringUtils.isNotBlank(query.getKeyWord()), "concat(ifnull(c_code, ''), ifnull(c_name, ''), ifnull(c_distract_name, ''), ifnull(c_alone_load, ''))", ScriptConditionEnum.Like, query.getKeyWord())
                .where(ObjectUtils.isNotEmpty(query.getMapRangesPoints()), sqlBuilder.toString(), ScriptConditionEnum.Eq, 0)
        ;
        if (ObjectUtils.isNotEmpty(query.getTreeHeight())) {
            sqlQueryMate.where(ObjectUtils.isNotEmpty(query.getTreeHeight()), "c_tree_height", ScriptConditionEnum.LessThanEq, (query.getTreeHeight() + 1))
                    .where(ObjectUtils.isNotEmpty(query.getTreeHeight()), "c_tree_height", ScriptConditionEnum.GreaterThanEq, (query.getTreeHeight() - 1));

        }
        if (ObjectUtils.isNotEmpty(query.getTreeWidth())) {
            sqlQueryMate
                    .where(ObjectUtils.isNotEmpty(query.getTreeWidth()), "c_tree_width", ScriptConditionEnum.GreaterThanEq, (query.getTreeWidth() - 1))
                    .where(ObjectUtils.isNotEmpty(query.getTreeWidth()), "c_tree_width", ScriptConditionEnum.LessThanEq, (query.getTreeWidth() + 1));

        }
        return getDataPager(sqlQueryMate, pageSize, pageIndex, GreTreeFamousVO.class);
    }

    @Override
    public GreTreeFamous getEntityByColumn(String key, String value) {
        KeyValue<String> map = new KeyValue<>();
        map.key = key;
        map.value = value;
        return getEntityByColumnEqValue(map);
    }

    @Override
    public DataPager<Map> getList(DataPager<Map> page, Map map) {
        return null;
    }

    @Override
    public DataPager<GreTreeFamousRecordVO> getRecordList(GreTreeFamousRecordQuery query) {
        String table = "t_gre_tree_famous_record t\n" +
                "        JOIN t_sys_user tsu ON t.c_db_create_id = tsu.c_id";
        String columns = "t.*,\n" +
                "        tsu.c_user_name `c_db_create_name`";
        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns).setOrderBy("c_db_create_date DESC");
        sqlQueryMate.where(true, "t.c_db_status", ScriptConditionEnum.Eq, 0)
                .where(query.getTreeIntId() != null, "c_tree_int_id", ScriptConditionEnum.Eq, query.getTreeIntId());
        int pageIndex = query.pageIndex - 1;
        int pageSize = query.pageSize;
        return getDataPager(sqlQueryMate, pageSize, pageIndex, GreTreeFamousRecordVO.class);
    }

    @Override
    public void deleteByIds(List<String> ids, Integer userId) {

    }

    @Override
    public void update(GreTreeFamous greTreeFamous, Integer userId) {

    }

    @Override
    public void dataPublics(List<Integer> ids, Integer userId) {

    }

    @Override
    public void insert(GreTreeFamous greTreeFamous, Integer userId) {
    }

    @Override
    public GreTreeFamous getById(Integer id) {
        SqlQueryMate sqlQueryMate = new SqlQueryMate("t_gre_tree_famous tgtf");
        sqlQueryMate.setColumns("tgtf.*,( SELECT tsm.c_code FROM t_sys_map tsm WHERE tsm.c_map_id = tgtf.c_distract_id ) distractCode,\n" +
                "( SELECT tsm1.c_code FROM t_sys_map tsm1 WHERE tsm1.c_map_id = tgtf.c_street_id ) streetCode," +
                "( SELECT tsm2.c_code FROM t_sys_map tsm2 WHERE tsm2.c_map_id = tgtf.c_community_id ) communityNumber ");
        sqlQueryMate.where(true, "tgtf.c_db_status", ScriptConditionEnum.Eq, 0)
                .where(true, "tgtf.c_id", ScriptConditionEnum.Eq, id);
        return selectOne(sqlQueryMate, GreTreeFamous.class);
    }

    @Override
    public void insertSql(GreTreeFamous greTreeFamous) {

    }

    @Override
    public Map<String, Object> getSituation() {
        return null;
    }

    @Override
    public List<Map<String, Object>> getAreaCountStat() {
        return null;
    }

    @Override
    public List<Map<String, Object>> listMaps() {
        return getMap("select c_tree_id `id`,c_name `value`,c_code `code` from t_gre_tree_famous where c_db_status= 0 and c_famous_type_id =1");
    }

    @Override
    public Integer updateAge(Integer year) {
        return null;
    }

    @Override
    public void updateFamousType() {

    }

    @Override
    public List<Map<String, Object>> getAllTreeFamousForMap(int famous_type_id) {
        return null;
    }

    @Override
    public List<GreTreeFamous> getListByCommunityCode(String communityCode, String name) {
        return null;
    }

    @Override
    public GreTreeFamous getByCommunityCodeAndCode(String communityCode, String code) {
        return null;
    }

    @Override
    public List<GreTreeFamous> getListForQRCode(Integer type) {
        return null;
    }


    public void updateToWgs(StringBuilder geom) {

        String sql = "update t_gis_map_mask set geom = " + geom.toString() + "  where  code = '6666'";
        run(sql, CommandType.Text, dbCmd -> {
            Boolean data = dbCmd.executeNonQuery();
        });

    }

    public List<Map<String, Object>> getCodeList() {
        return getMap("select c_id id,c_code code,c_lng lng,c_lat lat,c_name name," +
                "c_manage_id  deptId,c_maintain_dept_id  companyId  from t_gre_tree_famous where c_db_status= 0 and c_famous_type_id =1");
    }

    public Map getCount(String name, String sectionId) {
        String table = "t_gre_tree_famous tp";

        String columns = "count(1) value,'古树名木' as name";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate
                .where(true, "tp.c_db_status", ScriptConditionEnum.Eq, 0)
                .where(StringUtils.isNotBlank(name), "tp.c_tree_kind", ScriptConditionEnum.Eq, name)
                .where(StringUtils.isNotBlank(sectionId), "tp.c_obj_name", ScriptConditionEnum.Eq, sectionId)


        ;
        return getOneMap(sqlQueryMate);

    }

    public List<GreTreeFamousVO> getTypeList(String sectionId) {
        String table = "t_gre_tree_famous tp";

        String columns = "distinct tp.c_tree_kind c_type, tp.c_tree_kind c_type_name ";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate
                .where(true, "tp.c_db_status", ScriptConditionEnum.Eq, 0)
                .where(StringUtils.isNotBlank(sectionId), "tp.c_obj_name", ScriptConditionEnum.Eq, sectionId)

        ;

        List<GreTreeFamousVO> list = selectList(sqlQueryMate, GreTreeFamousVO.class);
        for (GreTreeFamousVO greTreeFamousVO : list) {
            if (StringUtils.isNotEmpty(greTreeFamousVO.getType())) {
                Map map = getCount(greTreeFamousVO.getTypeName(), null);
                greTreeFamousVO.setTotalNumber(String.valueOf(map.get("value")));
            }
        }
        return list;
    }


    public DataPager<GreTreeFamousVO> getDateList(GreTreeFamousQuery query) {
        String table = "t_gre_tree_famous tgtf";

        String columns = "c_id, c_tree_id, CONCAT(c_code,'-',c_name) name, c_name_latin, c_tree_kind, c_tree_attr, c_street_id, c_street_name, c_distract_id, c_distract_name, c_community_id, c_community_name, c_lng, c_lat, c_lng2000, c_lat2000, c_grow_place, c_address, c_address_two, c_power, c_manage, c_manage_id, c_maintain_dept_id, c_maintain_dept, c_is_gp, c_source, c_tree_height, c_tree_old, c_protect_level, c_protect_level_name, c_tree_width, c_dewei, c_tree_state, c_east_west_crown, c_north_south_crown, c_avg_crown, c_altitude, c_aspect, c_slope, c_status, c_soil, c_tightness, c_grow_type, c_grow_ambient, c_plant_user, c_plant_date, c_history, c_record, c_long_desc, c_name_comm, c_tree_trait, c_examine_code, c_famous_type_id, c_famous_type_name, c_maintain_user, c_community_code, c_is_protect, c_desc, c_db_create_id, c_db_create_date, c_db_status, c_data_public, c_year, c_code_two, c_state, c_plant_units, c_obj_name";

        int pageIndex = query.pageIndex - 1;
        int pageSize = query.pageSize;

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns).setOrderBy("tgtf.c_code asc");
        sqlQueryMate
                .where(true, "tgtf.c_db_status", ScriptConditionEnum.Eq, 0)
                .where(StringUtils.isNoneBlank(query.beginDate) && StringUtils.isNoneBlank(query.endDate), "c_db_create_date", ScriptConditionEnum.GreaterThanEq, query.beginDate)
                .where(StringUtils.isNoneBlank(query.beginDate) && StringUtils.isNoneBlank(query.endDate), "c_db_create_date", ScriptConditionEnum.LessThanEq, query.endDate)
                .where(StringUtils.isNoneBlank(query.getCommunityCode()), "c_community_code", ScriptConditionEnum.Like, query.getCommunityCode())
                .where(StringUtils.isNoneBlank(query.getCodeTwo()), "c_code_two", ScriptConditionEnum.Like, query.getCodeTwo())
                .where(StringUtils.isNoneBlank(query.getTreeState()), "c_tree_state", ScriptConditionEnum.Like, query.getTreeState())
                .where(StringUtils.isNoneBlank(query.getTreeAttr()), "c_tree_attr", ScriptConditionEnum.Like, query.getTreeAttr())
                .where(StringUtils.isNoneBlank(query.getFamousTypeName()), "c_famous_type_name", ScriptConditionEnum.Like, query.getFamousTypeName())
                .where(ObjectUtils.isNotEmpty(query.getFamousTypeId()), "c_famous_type_id", ScriptConditionEnum.Eq, query.getFamousTypeId())
                .where(StringUtils.isNoneBlank(query.getKeyWord()), "concat(ifnull(c_code_two, ''), ifnull(c_name, ''), ifnull(c_distract_name, ''), ifnull(c_tree_state, ''), ifnull(c_tree_old, ''))",
                        ScriptConditionEnum.Like, query.getKeyWord())
                .where(StringUtils.isNotBlank(query.getCode()), "tgtf.c_code", ScriptConditionEnum.Like, query.getCode())
                .where(StringUtils.isNotBlank(query.getName()), "tgtf.c_name", ScriptConditionEnum.Like, query.getName())
                .where(StringUtils.isNotBlank(query.getTreeKind()), "tgtf.c_tree_kind", ScriptConditionEnum.Like, query.getTreeKind())
                .where(StringUtils.isNotBlank(query.getAloneLoad()), "tgtf.c_alone_load", ScriptConditionEnum.Like, query.getAloneLoad())
                .where(StringUtils.isNotBlank(query.getDistractId()), "tgtf.c_distract_id", ScriptConditionEnum.Like, query.getDistractId())
                .where(StringUtils.isNotBlank(query.beginDate), "tgtf.c_db_create_date", ScriptConditionEnum.GreaterThanEq, query.beginDate)
                .where(StringUtils.isNotBlank(query.endDate), "tgtf.c_db_create_date", ScriptConditionEnum.LessThanEq, query.endDate)
                .where(StringUtils.isNotBlank(query.getKeyWord()), "concat(ifnull(c_code, ''), ifnull(c_name, ''), ifnull(c_distract_name, ''), ifnull(c_alone_load, ''))", ScriptConditionEnum.Like, query.getKeyWord())
        ;
        if (ObjectUtils.isNotEmpty(query.getTreeHeight())) {
            sqlQueryMate.where(ObjectUtils.isNotEmpty(query.getTreeHeight()), "c_tree_height", ScriptConditionEnum.LessThanEq, (query.getTreeHeight() + 1))
                    .where(ObjectUtils.isNotEmpty(query.getTreeHeight()), "c_tree_height", ScriptConditionEnum.GreaterThanEq, (query.getTreeHeight() - 1));

        }
        if (ObjectUtils.isNotEmpty(query.getTreeWidth())) {
            sqlQueryMate
                    .where(ObjectUtils.isNotEmpty(query.getTreeWidth()), "c_tree_width", ScriptConditionEnum.GreaterThanEq, (query.getTreeWidth() - 1))
                    .where(ObjectUtils.isNotEmpty(query.getTreeWidth()), "c_tree_width", ScriptConditionEnum.LessThanEq, (query.getTreeWidth() + 1));

        }

        return getDataPager(sqlQueryMate, pageSize, pageIndex, GreTreeFamousVO.class);
    }

}
